package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import es.ats.codington.festival.mappers.PeopleMapper;
import com.mysql.jdbc.Statement;

import es.ats.codington.festival.mappers.VisitorMapper;

import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.interfaces.daos.IVisitorDAO;

/**
 * The Class VisitorDAOImpl.
 *
 * @author 5Cs
 */
public class VisitorDAOImpl implements IVisitorDAO {
	
	/** The jdbc template object. */
	private JdbcTemplate jdbcTemplateObject;
	
	/** The query properties. */
	private Properties queryProperties;

	
	/**
	 * Instantiates a new visitor dao impl.
	 *
	 * @param jdbcTemplateObject the jdbc template object
	 * @param queryProperties the query properties
	 */
	public VisitorDAOImpl(JdbcTemplate jdbcTemplateObject,Properties queryProperties ) {
		this.jdbcTemplateObject = jdbcTemplateObject;
		this.queryProperties = queryProperties;
	} 
	

	/**
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#updateVisitorPassDAO(es.ats.codington.festival.domain.Visitor)
	 */
	public boolean updateVisitorPassDAO(final Visitor visitor)throws SQLException, ClassNotFoundException{
		int affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("updateVisitorPass"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1, visitor.getUsername());
				statement.setString(2, visitor.getPassword());
				if (visitor.isAdmin()) 	statement.setBoolean(3, true);
				else statement.setBoolean(3, false);
				statement.setInt(4, visitor.getVisitorId());
			}			
		});					
		return affectedRows == 1;
	}
	
	/**
	 *  
	 *
	 * @param visitor the visitor
	 * @return the int
	 * @throws SQLException the SQL exception
	 * @throws ClassNotFoundException the class not found exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#registerVisitorDAO(java.sql.Connection, es.ats.codington.festival.domain.Visitor)
	 */
	@Override
	public boolean registerVisitorDAO(final Visitor visitor) throws SQLException, ClassNotFoundException{
		int affectedRows = 0;
		
		KeyHolder holder = new GeneratedKeyHolder();
		List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("isAcountExists"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1, visitor.getUsername());
			}			
		}, new VisitorMapper());
			
		if(visitors.size() == 0){					
			jdbcTemplateObject.update(new PreparedStatementCreator() {           

                @Override
                public PreparedStatement createPreparedStatement(Connection connection)
                        throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(queryProperties.getProperty("insertPeople"), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, visitor.getName());
                    ps.setString(2, visitor.getLastName());
                    ps.setInt(3, visitor.getAge());
                    ps.setString(4, visitor.getAddress());
                    ps.setString(5, visitor.getPhoneNumber());
                    return ps;
                }
            }, holder);
			
			final int newVisitorId = holder.getKey().intValue();
				
			affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("insertVisitor"), 
					new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)
						throws SQLException {
					statement.setString(1, visitor.getUsername());
					statement.setString(2, visitor.getPassword());
					statement.setBoolean(3, false);
					statement.setInt(4, newVisitorId);
				}			
			});		
			if(affectedRows == 1){
				
			}
			return affectedRows == 1;
		}
		return false;	
	}

	/**
	 * Delete visitor dao.
	 *
	 * @param con the con
	 * @param idVisitor the id visitor
	 * @return the int
	 * @throws Exception the exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#deleteVisitorDAO(java.sql.Connection, int)
	 */
	public boolean deleteVisitorDAO(final int idVisitor) throws Exception{
		int affectedRows = 0;	
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("delete_visitorEvents"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, idVisitor);
			}
		});
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("delete_visitor"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, idVisitor);
			}
		});
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("delete_people"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1,idVisitor);
			}
		});
		return affectedRows == 1;
	}
	
	/**
	 *  
	 *
	 * @param visitor the visitor
	 * @return the int
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#updateVisitorDAO(java.sql.Connection, es.ats.codington.festival.domain.Visitor)
	 */
	public int updateVisitorDAO(final Visitor visitor){
		
		int affectedRows = 0;	
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("change_people"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1, visitor.getName());
				statement.setString(2, visitor.getLastName());
				statement.setInt(3, visitor.getAge());
				statement.setString(4, visitor.getAddress());
				statement.setString(5, visitor.getPhoneNumber());
				statement.setInt(6, visitor.getVisitorId());
			}
		});
		
		affectedRows = jdbcTemplateObject.update(queryProperties.getProperty("change_visitor"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1,visitor.getUsername());
				statement.setString(2,visitor.getPassword());
				statement.setBoolean(3,visitor.isAdmin());
				statement.setInt(4,visitor.getVisitorId());
			}
		});
		

		return affectedRows;
	}  
	
		
	/**
	 * Search visitor dao by id.
	 *
	 * @param idVisitor the id visitor
	 * @return the visitor
	 * @throws SQLException the SQL exception
	 * @throws ClassNotFoundException the class not found exception
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#searchVisitorDAOById(java.sql.Connection, int)
	 */
	
	public Visitor searchVisitorDAOById(final int idVisitor) throws SQLException, ClassNotFoundException{	
		List<Visitor> visitors =  jdbcTemplateObject.query(queryProperties.getProperty("select_VisitorID"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, idVisitor);
			}
		}, new VisitorMapper());
		 Visitor visitor = visitors.get(0);
		
		 List<Visitor> peoples =  jdbcTemplateObject.query(queryProperties.getProperty("select_PeopleID"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setInt(1, idVisitor);
			}
		}, new PeopleMapper());
		 Visitor people = peoples.get(0);
		 visitor.setName(people.getName());
		 visitor.setLastName(people.getLastName());
		 visitor.setAge(people.getAge());
		 visitor.setAddress(people.getAddress());
		 visitor.setPhoneNumber(people.getPhoneNumber());
		
		return visitor;
	}
	
	/**
	 * Search all visitor dao.
	 *
	 * @param con the con
	 * @return the array list
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#searchAllVisitorDAO(java.sql.Connection)
	 */
	public List<Visitor> searchAllVisitorDAO(int idAdmin) throws SQLException, ClassNotFoundException {	 
		List<Visitor> visitors = null;
		visitors = jdbcTemplateObject.query(queryProperties.getProperty("allvisitors"), 
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
			}			
		}, new VisitorMapper());
		
		List<Visitor> people =  jdbcTemplateObject.query(queryProperties.getProperty("allpeople"),
				new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
			}
		}, new PeopleMapper());
		
		List<Visitor> listVisitor = new ArrayList<Visitor>();
		int i = -1;		
		while (i<(visitors.size()-1)) {
			i++;
			int idVisitor = people.get(i).getVisitorId();
			if(idVisitor != idAdmin){
				visitors.get(i).setName(people.get(i).getName());
				visitors.get(i).setLastName(people.get(i).getLastName());
				visitors.get(i).setAge(people.get(i).getAge());
				visitors.get(i).setAddress(people.get(i).getAddress());
				visitors.get(i).setPhoneNumber(people.get(i).getPhoneNumber());
				listVisitor.add(visitors.get(i));
			}
			
		}
		
		return listVisitor;
	}
	

	/**
	 * @see es.ats.codington.festival.interfaces.daos.IVisitorDAO#loginDAO(es.ats.codington.festival.domain.Visitor)
	 */
	public Visitor loginDAO(final Visitor visitor) throws SQLException, ClassNotFoundException{	
		List<Visitor> visitors =  jdbcTemplateObject.query(queryProperties.getProperty("selec_login"),new PreparedStatementSetter(){
			@Override
			public void setValues(PreparedStatement statement)
					throws SQLException {
				statement.setString(1, visitor.getUsername());
				statement.setString(2, visitor.getPassword());
			}
		}, new VisitorMapper());
		return visitors.get(0);
	}
}
